Present invention embodiments are related to relational database systems for performing transactional table truncations. In particular, present invention embodiments are related to performing a truncate command while permitting a read operation on a truncated table, wherein the rows in the truncated table are visible to at least one concurrent transaction and invisible to subsequent transactions.
It is quite common, in database applications, to use a table by performing some analysis or queries, empty out the table, and then reuse the table by loading new data into the table in order to repeat the analysis or perform some other analysis. In database management systems (DBMSs) that support SQL, a standard relational database language, a table may be emptied by using a DELETE FROM <table> statement with no qualification, such as a “WHERE clause”. However, individually deleting all rows (or marking them deleted in a multi-version DBMS) in a database table, as DELETE does, is an expensive operation due to locating rows via a full table scan, locking or other concurrency control overhead, and logging overhead for transaction recovery and durability. In some DBMSs, “transaction log full” errors may occur when insufficient log space was allocated.
ISO SQL: 2008 Standard includes a TRUNCATE TABLE operation for “fast emptying” of a table without having to examine any rows. Storage associated with the table is freed, or marked available for reuse, locking and logging are greatly streamlined, and “triggers” defined for row deletion are neither checked nor executed. For very large tables, a TRUNCATE operation can execute hundreds or thousands of times faster than a DELETE operation for all rows.
It is desirable to perform “emptying out” a table and loading fresh data into the table as a single atomic transaction such that current applications and queries do not see an intermediate empty state of the table. A concurrent query should either see the rows in the table before the emptying out, or the rows with the loaded fresh data after the emptying out. Oracle® (Oracle is a registered service mark of Oracle International Corporation of Redwood City, Calif.), which is a market-leading relational database management system (RDBMS), executes TRUNCATE as an implicit transaction by itself and also implicitly commits a current transaction from a same session, if there is one. Therefore, Oracle® cannot execute TRUNCATE as part of a larger transaction. The Informix® RDBMS (Informix is a registered trademark of International Business Machines Corporation of Armonk, N.Y.) is somewhat more tolerant than Oracle®, in that TRUNCATE can execute in a currently open transaction, if there is one, but must be followed by a COMMIT of the transaction (or ROLLBACK, if the changes from the transaction are to be discarded). In BOTH Oracle® and Informix®, it is not possible for a TRUNCATE to be followed by an INSERT or multiple serial or parallel INSERTs in order to load fresh data in a same transaction. As a result, applications may either execute a TRUNCATE followed by an INSERT or INSERTs as two separate transactions, making it possible for concurrent queries to see an empty table, or applications may execute a more expensive DELETE followed by the INSERT or INSERTs in a single atomic transaction.
Microsoft SQL Server, IBM PureData® (PureData is a registered trademark of International Business Machines Corporation of Armonk, N.Y.) for Analytics, and PostgreSQL permit TRUNCATE to be followed by other SQL commands, such as INSERT, in a same atomic transaction and permit transactions that truncate tables to be rolled back or committed as a whole. Therefore, in these RDBMs, it is possible to atomically “empty and reload” efficiently without a risk of concurrent queries seeing the table in an empty, inconsistent state. However, due to the fact that COMMIT of a truncating transaction frees up storage for the truncated tables, other problems may occur.
When using SQL Standard Repeatable Read or Serializable Transaction Isolation, or Snapshot Isolation, the following series of queries, where Ti are transactions and Qj are queries, runs into problems:
T0-Q1: Insert into TBL <1,000 rows>;
T0: Commit;
T1: Start with Repeatable Read, Snapshot, or Serializable Isolation. T1 will see only results of transactions that committed before T1 started, such as T0.
T1-Q1: Select count(*) from TBL;—returns 1,000
T2-Q2: Truncate Table TBL;
T2-Q3: Insert into TBL <500 rows>;
T2: Commit;
T3-Q1: Select count(*) from TBL;—returns 500, since T3 started after T2 committed and therefore sees all of T2's changes.
T1-Q1: Select count(*) from TBL;
One would expect T1-Q1 to return 1,000 rows because T1 did not see any of T2's changes (T2 committed after T1 started) under Repeatable Read, Snapshot Isolation or Serializable Isolation. However, in known DBMSs, storage space occupied by the original 1,000 rows in TBL is freed when T2 commits. Therefore, it is no longer possible to determine an original row count. After truncation, the table appears empty to all concurrent transactions, whether or not a concurrent transaction is using a snapshot taken before the truncation occurred.